package finalproject.web;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.rowset.RowSetProvider;
import javax.sql.rowset.WebRowSet;

public class DBTool {
	// 封装与数据库的相关操作

	// 连接数据库Connection
	public static Connection getConn() {
		// xr.yu:1230321
		// yuHui.li:1230321
		Connection conn = null;
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mariadb://localhost:3306/db?user=root&password=1230321");
			Statement stmt = conn.createStatement();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	// sql-->Db
	// insert update delete -> int
	public static int doSQL(String sql) {
		Connection conn = getConn();
		Statement stmt = null;
		int r = 0;
		try {
			stmt = conn.createStatement();
			System.out.println("sql=" + sql);
			r = stmt.executeUpdate(sql);
			System.out.println("更改行数：" + r);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("sql=" + sql);
		return r;
	}

	// select rs->DB
	public static ResultSet query(String sql) {
		Connection conn = getConn();
		ResultSet rs = null;
		try {
			Statement stmt = conn.createStatement();
			System.out.println("sql=" + sql);
			rs = stmt.executeQuery(sql);

			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("sql=" + sql);
		return rs;
	}

	// WebRowSet
	public static WebRowSet getRs(String sql) {
		Connection conn = getConn();
		WebRowSet rs2 = null;
		try {
			Statement stmt = conn.createStatement();
			System.out.println("sql=" + sql);
			ResultSet rSet = stmt.executeQuery("sql=" + sql);
			// rSrt-->wrs
			rs2 = RowSetProvider.newFactory().createWebRowSet();
			rs2.populate(rSet);

			rSet.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("sql=" + sql);
		return rs2;
	}

	public static void closeConnection(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
